-- kafka
-- hdp_teu_dpd_dw_wmda_log_ev_25664439184055

select
    k2.buid,
    count(case when k2.act_type='曝光' then 1 end) as `当日劲·爆曝光次数`,
    max(case when act_type='曝光' then real_event_time end) as `当日劲·爆最近一次曝光时间`,
    max(case when act_type='支付点击' then real_event_time end) as `当日劲·爆最近一次曝光点击支付时间`
from (
-- 商业表
select
	cast(regexp_replace(user_id,'\"','') as bigint) as buid,
	from_unixtime(cast(substr(dt_ts_event,1,10) as bigint),'yyyy-MM-dd HH:mm:ss') as real_event_time,
	'融合弹窗' as page_type,
	case
	    when event_id in ('10020001','10010001') then '曝光'
		when event_id in ('10020002','10010002') then '支付点击'
		when event_id in ('10020003','10010003') then '成功'
		when event_id in ('10020004','10010004') then '失败'
    end as act_type,
	user_id
from hdp_teu_dpd_dw_wmda_log_ev_25664439184055
where dt='${dateSuffix}'
and event_id in ('10020001','10020002','10020003','10020004','10010001','10010002','10010003','10010004')
and kv['productType'] in ('10','11')    --10:劲爆单购；11：普通单购

union all
select
	cast(regexp_replace(user_id,'\"','') as bigint) as buid,
	from_unixtime(cast(substr(dt_ts_event,1,10) as bigint),'yyyy-MM-dd HH:mm:ss') as real_event_time,
	'单购页' as page_type,
	case
	    when event_id in ('10320001','10310001') then '曝光'
        when event_id in ('10320002','10310002') then '支付点击'
        when event_id in ('10320003','10310003') then '成功'
        when event_id in ('10320004','10310004') then '失败'
    end as act_type,
	user_id
from hdp_teu_dpd_dw_wmda_log_ev_25664439184055
where dt='${dateSuffix}'
	and event_id in ('10320001','10320002','10320003','10320004','10310001','10310002','10310003','10310004')
	and kv['productType'] in ('10','11')
)k3
group by k2.buid
